package DBLayer;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import ModelLayer.Customer;
import ModelLayer.SalesOrder;
import java.text.DateFormat;
import java.text.SimpleDateFormat;

public class DBSalesOrder implements IFDBSalesOrder{
	private DateFormat df = new SimpleDateFormat("yyyy/MM/dd");
	private Connection con;
	public DBSalesOrder(){
		con = DBConnection.getInstance().getDBcon();
	}
	public void createSalesOrder(SalesOrder salesOrder) throws SQLException {
		int club;
		if(salesOrder.getclub()){
			club = 1;
		}else{
			club = 0;
		}
		String query = "INSERT into SalesOrder values(" +
				salesOrder.getSaleID()+"," +
				salesOrder.getdate()+"," +
				salesOrder.getamount()+",'"+
				salesOrder.getdeliveryStatus()+"'," +
				salesOrder.getdeliveryDate()+"," +
				salesOrder.getCustomer().getCprNo()+"," +
				club +"," +
				null+")";
		                     
		      System.out.println("insert : " + query);		  					
		      try{ // insert new product
		            Statement stmt = con.createStatement();
			 	  	stmt.setQueryTimeout(5);
			 	  	stmt.executeUpdate(query);
			 	  	stmt.close();
				}//end try	
			 	catch(SQLException ex){
			 	  	System.out.println("Insert exception in sales order db: "+ex);
			   }
	}
	public void confirmSalesOrder(SalesOrder salesOrder) throws SQLException, Exception{
		int club;
		if(salesOrder.getclub()){
			club = 1;
		}else{
			club = 0;
		}
		String query = "UPDATE SalesOrder SET " +
				"date = '"+df.format(salesOrder.getdate())+"'," +
				"amount = "+salesOrder.getamount()+"," +
				"delivaryStatus = '"+salesOrder.getdeliveryStatus()+"'," +
				"delivaryDate = '"+df.format(salesOrder.getdeliveryDate())+"'," +
				"cpr = '"+salesOrder.getCustomer().getCprNo()+"'," +
				"club = "+club+" "+
				"WHERE saleID = "+salesOrder.getSaleID();
	                System.out.println("Update query:" + query);
	  		try{ 
		 		Statement stmt = con.createStatement();
		 		stmt.setQueryTimeout(5);
		 	 	stmt.executeUpdate(query);    
		 	 	stmt.close();
			}
		 	catch(SQLException ex){
		 	 	System.out.println("Update exception in sales order db: "+ex);
		  	}
	}
	public SalesOrder findSalesOrder(int saleID) throws SQLException {
		String wClause = "saleID = "+saleID;
		return singleWhere(wClause);
	}
	public void updateSalesOrderInvoice(SalesOrder salesOrder){
		String query = "UPDATE SalesOrder SET" +
				"invoiceNo = "+salesOrder.getinvoiceNo() +
				"WHERE saleID = "+salesOrder.getSaleID();
	                System.out.println("Update sales order invoice query:" + query);
	  		try{ 
		 		Statement stmt = con.createStatement();
		 		stmt.setQueryTimeout(5);
		 	 	stmt.executeUpdate(query);
		 	 	stmt.close();
			}
		 	catch(SQLException ex){
		 	 	System.out.println("Update exception in sales order invoice db: "+ex);
		  	}
	}
	private SalesOrder singleWhere(String wClause)
	{
		ResultSet results;
		SalesOrder salesOrderObj = new SalesOrder();
	    String query =  buildQuery(wClause);
        System.out.println(query);
		try{ // read the product from the database
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		
	 		if( results.next() ){
	 			salesOrderObj = buildSalesOrder(results);
                            stmt.close();
			}
		}	
	 	catch(Exception e){
	 		System.out.println("Query exception: "+e);
	 	}
		return salesOrderObj;
	}
	private String buildQuery(String wClause)
	{
	    String query="SELECT * FROM SalesOrder ";
		
		if (wClause.length()>0)
			query= query+"WHERE "+ wClause;
			
		return query;
	}
	private SalesOrder buildSalesOrder(ResultSet results)
    {   
		SalesOrder salesOrderObj = new SalesOrder();
        try{
        	salesOrderObj.setSaleID(results.getInt(1));
        	salesOrderObj.setdate(results.getDate(2));
        	salesOrderObj.setamount(results.getDouble(3));
        	salesOrderObj.setdeliveryStatus(results.getString(4));
        	salesOrderObj.setdeliveryDate(results.getDate(5));
        	salesOrderObj.setCustomer(new Customer(results.getString(6)));
        	salesOrderObj.setclub(results.getBoolean(7));
        	salesOrderObj.setinvoiceNo(results.getInt(8));
        }
       catch(Exception e)
       {
           System.out.println("Error in building the sales order object");
       }
       return salesOrderObj;
    }
	public int getMaxSaleID(){
		int nextSalesOrder = GetMax.getMaxId("Select max(saleID) from SalesOrder");
		nextSalesOrder = nextSalesOrder + 1;
        return nextSalesOrder;
	}
	public int getLatestSaleID(){
		int nextSalesOrder = GetMax.getMaxId("Select max(saleID) from SalesOrder");
        return nextSalesOrder;
	}
}